﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using SimpleUnitity.Log;

namespace SimpleUnitity.Database.Extension
{
    /// <summary>
    /// DataHelper Package DbTool With Log
    /// </summary>
    public class DataHelper : DbTool
    {
        private readonly ILog log;
        private readonly DbTool tools;

        public bool SyncLog { get; set; }

        public ILog Log
        {
            get { return log; }
        }
        
        public DataHelper(DbTool tools, ILog log)
            : base(tools)
        {
            this.tools = tools;
            if (log != null)
            {
                this.log = log;
                this.BeforeExecute += new ExecuteEventHandler(DataHelper_BeforeExecute);
                this.OnError += new ErrorEventHandle(DataHelper_OnError);
            }
        }

        private void WriteLog(string message, ELogLevel level)
        {
            if (SyncLog == true)
            {
                LogHelper.Write(log, message, level);
            }
            else
            {
                LogHelper.AsyncWrite(log, message, level);
            }
        }

        void DataHelper_OnError(DbTool sender, Exception exception)
        {
            WriteLog(exception.Message, ELogLevel.Error);
            WriteLog(exception.ToString(), ELogLevel.Track);
        }

        void DataHelper_BeforeExecute(DbTool sender, IDbCommand command)
        {
            string sql = command.CommandText;
            foreach (IDataParameter param in command.Parameters)
            {
                if (param.Value == null || param.Value is DBNull)
                {
                    sql = sql.Replace(param.ParameterName, "NULL");
                }
                else
                {
                    sql = sql.Replace(param.ParameterName, string.Format("'{0}'", param.Value));
                }
            }
            WriteLog(sql, ELogLevel.Debug);
        }

        private ELogLevel logLevel = ELogLevel.Debug;

        public ELogLevel LogLevel
        {
            get { return logLevel; }
            set { logLevel = value; }
        }

        public string IdentitySQL { get; set; }

        public override string ParameterPrefix
        {
            get { return tools.ParameterPrefix; }
        }

        public override string StatementPrefix
        {
            get { return tools.StatementPrefix; }
        }

        public override string StatementSuffix
        {
            get { return tools.StatementSuffix; }
        }

        #region Helper Function
        private string GetConditionString(DbData data)
        {
            string conditions = " 1=1 ";
            if (data == null || data.GetFields().Length == 0)
            {
                return conditions;
            }
            List<string> nullFields = new List<string>();
            foreach (string field in data.GetFields())
            {
                if (data[field] == null || data[field] is DBNull)
                {
                    conditions += string.Format(" AND {1}{0}{2} IS NULL", field, StatementPrefix, StatementSuffix);
                    nullFields.Add(field);
                }
                else
                {
                    conditions += string.Format(" AND {1}{0}{2}={3}{0}", field, StatementPrefix, StatementSuffix, ParameterPrefix);
                }
            }
            foreach (string field in nullFields)
            {
                data.Remove(field);
            }
            return conditions;
        }

        public int Insert(string table, DbData data)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string columns = "";
            string values = "";
            foreach (string field in data.GetFields())
            {
                columns += string.Format(", {1}{0}{2}", field, StatementPrefix, StatementSuffix);
                values += string.Format(", {1}{0}", field, ParameterPrefix);
            }
            if (columns.Length > 0)
            {
                columns = columns.Substring(1);
                values = values.Substring(1);
            }
            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, columns, values);
            return ExecuteNonQuery(sql, data.ToDictionary());
        }

        public int Update(string table, DbData newData, DbData condition)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string sets = "";
            foreach (string field in newData.GetFields())
            {
                sets += string.Format(", {1}{0}{2}={3}{0}", field, StatementPrefix, StatementSuffix, ParameterPrefix);
            }
            if (sets.Length > 0)
            {
                sets = sets.Substring(1);
            }
            string wheres = GetConditionString(condition);
            string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", table, sets, wheres);

            DbData newArgs = new DbData();
            newArgs.SetRange(newData.ToDictionary());
            if (condition != null)
            {
                newArgs.SetRange(condition.ToDictionary());
            }
            return ExecuteNonQuery(sql, newArgs.ToDictionary());
        }

        public int Update(string table, DbData newData, string conditionColumn, object conditionValue)
        {
            DbData condition = new DbData();
            if (conditionColumn != null)
            {
                condition[conditionColumn] = conditionValue;
            }
            return Update(table, newData, condition);
        }

        public int Delete(string table, DbData condition)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string wheres = GetConditionString(condition);
            string sql = string.Format("DELETE FROM {0} WHERE {1}", table, wheres);
            return ExecuteNonQuery(sql, condition.ToDictionary());
        }

        public int Delete(string table, string conditionColumn, object conditionValue)
        {
            DbData condition = new DbData();
            if (conditionColumn != null)
            {
                condition[conditionColumn] = conditionValue;
            }
            return Delete(table, condition);
        }

        public DbData SelectInstance(string table, DbData condition)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string wheres = GetConditionString(condition);
            string sql = string.Format("SELECT * FROM {0} WHERE {1}", table, wheres);
            return QueryInstance(sql, condition);
        }

        public DbData SelectInstance(string table, string conditionColumn, object conditionValue)
        {
            DbData condition = new DbData();
            if (conditionColumn != null)
            {
                condition[conditionColumn] = conditionValue;
            }
            return SelectInstance(table, condition);
        }

        public IList<DbData> Select(string table)
        {
            return Select(table, null);
        }

        public IList<DbData> Select(string table, DbData condition)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string wheres = GetConditionString(condition);
            string sql = string.Format("SELECT * FROM {0} WHERE {1}", table, wheres);
            return Query(sql, condition);
        }

        public IList<DbData> Select(string table, string conditionColumn, object conditionValue)
        {
            DbData condition = new DbData();
            if (conditionColumn != null)
            {
                condition[conditionColumn] = conditionValue;
            }
            return Select(table, condition);
        }

        public int Count(string table)
        {
            return Count(table, null);
        }

        public int Count(string table, string conditionColumn, object conditionValue)
        {
            DbData condition = new DbData();
            if (conditionColumn != null)
            {
                condition[conditionColumn] = conditionValue;
            }
            return Count(table, condition);
        }

        public int Count(string table, DbData condition)
        {
            table = string.Format("{1}{0}{2}", table, StatementPrefix, StatementSuffix);
            string wheres = GetConditionString(condition);
            string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}", table, wheres);
            IDictionary<string, object> args = null;
            if (condition != null)
            {
                args = condition.ToDictionary();
            }
            return Convert.ToInt32(ExecuteScalar(sql, args));
        }

        public DbData QueryInstance(string sql, DbData args)
        {
            IDictionary<string, object> maps = null;
            if (args != null)
            {
                maps = args.ToDictionary();
            }
            return ExecuteQueryForObject<DbData>(sql, CreateDbDataHandle, maps);
        }

        public DbData QueryInstance(string sql, params object[] args)
        {
            return ExecuteQueryForObject<DbData>(sql, CreateDbDataHandle, args);
        }

        public IList<DbData> Query(string sql, DbData args)
        {
            IDictionary<string, object> maps = null;
            if (args != null)
            {
                maps = args.ToDictionary();
            }
            return ExecuteQueryForList<DbData>(sql, CreateDbDataHandle, maps);

        }

        public IList<DbData> Query(string sql, params object[] args)
        {
            return ExecuteQueryForList<DbData>(sql, CreateDbDataHandle, args);
        }

        private static DbData CreateDbDataHandle(IDataRecord record)
        {
            DbData data = new DbData();
            string field = null;
            object value = null;
            for (int i = 0; i < record.FieldCount; i++)
            {
                field = record.GetName(i);
                value = record.GetValue(i);
                if (value is DBNull)
                {
                    value = null;
                }
                data[field] = value;
            }
            return data;
        }

        #endregion

        #region GetIdentity

        public int GetIdentity()
        {
            return Convert.ToInt32(ExecuteScalar(IdentitySQL));
        }

        #endregion
    }
}